{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入各种包。。。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 创建链接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = pymysql.Connect(host = 'localhost',\n",
    "                      port = 3306,\n",
    "                      user = 'root',\n",
    "                      password = '123456',\n",
    "                      database = 'recommend',\n",
    "                      charset = 'utf8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 建表，通过分析，可以把表分成5张，分别为其赋值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建第一个表——沟通结果：取名result,这个表里包含沟通结果，每种结果对应一个id\n",
    "sql_result = 'create table `result`(id int primary key auto_increment,result varchar(20));'\n",
    "# 创建第二个表——职业种类：取名category,这个表里包含职业类型，每种职业类型对应一个id\n",
    "sql_category = 'create table category(id int primary key auto_increment,category varchar(100) not null);'\n",
    "# 创建第三个表——顾客手机名单：取名customer,这个表里包含顾客的手机号tel，对应的id，然后要与第二个表职业种类的id建立外键关系。\n",
    "sql_customer = 'create table customer(id int primary key auto_increment,tel varchar(32),category_id int,constraint c1 foreign key (category_id) references category(id) on delete cascade);'\n",
    "# 创建第四个表——销售人员表：取名salesstaff,这个表里包含序列号，姓名，账号，密码4列\n",
    "sql_salesstaff = 'create table salesstaff(id int primary key auto_increment,name varchar(16),account varchar(16) not null unique,password varchar(16) not null);'\n",
    "# 创建第五个表——销售任务清单表;取名mission,这个表中把原来顾客的手机号改为id，然后与第三个顾客表的id，第四个销售人员的id建立外键关系。\n",
    "# 然后生成结果，所以还要与第一个沟通结果表建立外键关系。\n",
    "sql_mission = 'create table mission(id int primary key auto_increment,customer_id int not null,salesstaff_id int not null,createDate date not null,result_id int not null,constraint c2 foreign key(customer_id) references customer(id) on delete cascade,constraint c3 foreign key(salesstaff_id) references salesstaff(id) on delete cascade,constraint c4 foreign key(result_id)references result(id) on delete cascade);'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 执行命令"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 先创建一个指针\n",
    "cs = conn.cursor()\n",
    "# 将指令发给MySQL\n",
    "cs.execute(sql_result)\n",
    "cs.execute(sql_category)\n",
    "cs.execute(sql_customer)\n",
    "cs.execute(sql_salesstaff)\n",
    "cs.execute(sql_mission)\n",
    "# 执行指令\n",
    "conn.commit\n",
    "# 关闭指针，节约内存\n",
    "cs.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读取表并写入创建的数据库表结构中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>account</th>\n",
       "      <th>password</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>张艳</td>\n",
       "      <td>876024831567</td>\n",
       "      <td>b17b1ae95299f365d33f6f28766e34d8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李勇</td>\n",
       "      <td>156436662455</td>\n",
       "      <td>cf6088de6b5a5201db2c5d8449efdb15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>王平</td>\n",
       "      <td>260460121666</td>\n",
       "      <td>67768d6df4fc05b7d74c8b59e17a4ccf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>李强</td>\n",
       "      <td>775550594765</td>\n",
       "      <td>9b7a25dd2c8b79b8bd9c685d6858b223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>王芳</td>\n",
       "      <td>965918890050</td>\n",
       "      <td>17e9eba3e021cd4f4412a31dfd1c4f98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  name       account                          password\n",
       "0   张艳  876024831567  b17b1ae95299f365d33f6f28766e34d8\n",
       "1   李勇  156436662455  cf6088de6b5a5201db2c5d8449efdb15\n",
       "2   王平  260460121666  67768d6df4fc05b7d74c8b59e17a4ccf\n",
       "3   李强  775550594765  9b7a25dd2c8b79b8bd9c685d6858b223\n",
       "4   王芳  965918890050  17e9eba3e021cd4f4412a31dfd1c4f98"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取销售表：把销售人员的excel表中的三列数据导入构建的salesstaff表中\n",
    "df_salesstaff = pd.read_excel('F:\\软件\\pycharm\\资料\\第三周课程课件\\第三周第十节项目资料\\数据\\销售人员.xlsx',\n",
    "                            sheet_name = '销售信息')\n",
    "# 打印前五行\n",
    "df_salesstaff.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 附加：把name一列的名字遍历一遍，去掉名字首位的空格和换行符\n",
    "df_salesstaff['name'] = df_salesstaff['name'].apply(lambda name:name.strip())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1680\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1681\u001b[1;33m             \u001b[0mcur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1682\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    160\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 161\u001b[1;33m         \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    162\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mmogrify\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    139\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0margs\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 140\u001b[1;33m             \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mquery\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_escape_args\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    141\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: not all arguments converted during string formatting",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-54-bed3868e79a7>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m                      \u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m                      \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m                      \u001b[0mif_exists\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'append'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m                     )\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m   2610\u001b[0m             \u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2611\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2612\u001b[1;33m             \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2613\u001b[0m         )\n\u001b[0;32m   2614\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m    596\u001b[0m         \u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    597\u001b[0m         \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 598\u001b[1;33m         \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    599\u001b[0m     )\n\u001b[0;32m    600\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m   1825\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1826\u001b[0m         )\n\u001b[1;32m-> 1827\u001b[1;33m         \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1828\u001b[0m         \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1829\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mcreate\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    719\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    720\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 721\u001b[1;33m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    722\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"fail\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    723\u001b[0m                 \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"Table '{self.name}' already exists.\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexists\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    706\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    707\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mexists\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 708\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpd_sql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhas_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mschema\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    709\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    710\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0msql_schema\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mhas_table\u001b[1;34m(self, name, schema)\u001b[0m\n\u001b[0;32m   1836\u001b[0m         \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34mf\"SELECT name FROM sqlite_master WHERE type='table' AND name={wld};\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1837\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1838\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1839\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1840\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mget_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtable_name\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1691\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1692\u001b[0m             \u001b[0mex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mDatabaseError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"Execution failed on sql '{args[0]}': {exc}\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1693\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mex\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1694\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1695\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting"
     ]
    }
   ],
   "source": [
    "# 写入数据\n",
    "df_salesstaff.to_sql('salesstaff',\n",
    "                     conn,\n",
    "                     index=False,\n",
    "                     if_exists='append',\n",
    "                    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['人事-行政-后勤',\n",
       " '保健按摩',\n",
       " '保险',\n",
       " '其他职位',\n",
       " '农-林-牧-渔业',\n",
       " '制药-生物工程',\n",
       " '化工',\n",
       " '医院-医疗-护理',\n",
       " '司机-交通服务',\n",
       " '商超零工',\n",
       " '实习生-培训生-储备干部',\n",
       " '客服',\n",
       " '家政保洁-安保',\n",
       " '工厂零工',\n",
       " '市场-媒介-公关',\n",
       " '广告-会展-咨询',\n",
       " '建筑',\n",
       " '影视-娱乐-休闲',\n",
       " '志愿者-社会工作者',\n",
       " '快递-餐饮配送',\n",
       " '房产中介',\n",
       " '政府-非营利机构',\n",
       " '教育培训',\n",
       " '旅游',\n",
       " '日结零工',\n",
       " '普工-技工',\n",
       " '暑期零工',\n",
       " '服装-纺织-食品',\n",
       " '机械-仪器仪表',\n",
       " '汽车制造-服务',\n",
       " '法律',\n",
       " '淘宝职位',\n",
       " '物业管理',\n",
       " '物流-仓储',\n",
       " '环保-能源',\n",
       " '生产管理-研发',\n",
       " '电子-电气',\n",
       " '短期零工',\n",
       " '编辑-出版-印刷',\n",
       " '美容-美发',\n",
       " '美术-设计-创意',\n",
       " '翻译',\n",
       " '职业培训',\n",
       " '计算机-互联网-通信',\n",
       " '财务-审计-统计',\n",
       " '质控-安防',\n",
       " '贸易-采购',\n",
       " '超市-百货-零售',\n",
       " '运动健身',\n",
       " '酒店',\n",
       " '金融-银行-证券-投资',\n",
       " '销售11',\n",
       " '餐饮',\n",
       " '餐饮零工',\n",
       " '高级管理']"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取种类表——把客户信息表中的种类读取出来，放到构建的categorys表中\n",
    "categorys = pd.ExcelFile('F:\\软件\\pycharm\\资料\\第三周课程课件\\第三周第十节项目资料\\数据\\顾客信息.xlsx').sheet_names\n",
    "categorys\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'insert into category (content) values (\"人事-行政-后勤\"),(\"保健按摩\"),(\"保险\"),(\"其他职位\"),(\"农-林-牧-渔业\"),(\"制药-生物工程\"),(\"化工\"),(\"医院-医疗-护理\"),(\"司机-交通服务\"),(\"商超零工\"),(\"实习生-培训生-储备干部\"),(\"客服\"),(\"家政保洁-安保\"),(\"工厂零工\"),(\"市场-媒介-公关\"),(\"广告-会展-咨询\"),(\"建筑\"),(\"影视-娱乐-休闲\"),(\"志愿者-社会工作者\"),(\"快递-餐饮配送\"),(\"房产中介\"),(\"政府-非营利机构\"),(\"教育培训\"),(\"旅游\"),(\"日结零工\"),(\"普工-技工\"),(\"暑期零工\"),(\"服装-纺织-食品\"),(\"机械-仪器仪表\"),(\"汽车制造-服务\"),(\"法律\"),(\"淘宝职位\"),(\"物业管理\"),(\"物流-仓储\"),(\"环保-能源\"),(\"生产管理-研发\"),(\"电子-电气\"),(\"短期零工\"),(\"编辑-出版-印刷\"),(\"美容-美发\"),(\"美术-设计-创意\"),(\"翻译\"),(\"职业培训\"),(\"计算机-互联网-通信\"),(\"财务-审计-统计\"),(\"质控-安防\"),(\"贸易-采购\"),(\"超市-百货-零售\"),(\"运动健身\"),(\"酒店\"),(\"金融-银行-证券-投资\"),(\"销售11\"),(\"餐饮\"),(\"餐饮零工\"),(\"高级管理\");'"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 建立外键关系\n",
    "values = []\n",
    "for category in categorys:\n",
    "    values.append('(\"%s\")'% category)\n",
    "sql = 'insert into category (content) values %s;' % ','.join(values)\n",
    "sql\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['(\"人事-行政-后勤\")',\n",
       " '(\"保健按摩\")',\n",
       " '(\"保险\")',\n",
       " '(\"其他职位\")',\n",
       " '(\"农-林-牧-渔业\")',\n",
       " '(\"制药-生物工程\")',\n",
       " '(\"化工\")',\n",
       " '(\"医院-医疗-护理\")',\n",
       " '(\"司机-交通服务\")',\n",
       " '(\"商超零工\")',\n",
       " '(\"实习生-培训生-储备干部\")',\n",
       " '(\"客服\")',\n",
       " '(\"家政保洁-安保\")',\n",
       " '(\"工厂零工\")',\n",
       " '(\"市场-媒介-公关\")',\n",
       " '(\"广告-会展-咨询\")',\n",
       " '(\"建筑\")',\n",
       " '(\"影视-娱乐-休闲\")',\n",
       " '(\"志愿者-社会工作者\")',\n",
       " '(\"快递-餐饮配送\")',\n",
       " '(\"房产中介\")',\n",
       " '(\"政府-非营利机构\")',\n",
       " '(\"教育培训\")',\n",
       " '(\"旅游\")',\n",
       " '(\"日结零工\")',\n",
       " '(\"普工-技工\")',\n",
       " '(\"暑期零工\")',\n",
       " '(\"服装-纺织-食品\")',\n",
       " '(\"机械-仪器仪表\")',\n",
       " '(\"汽车制造-服务\")',\n",
       " '(\"法律\")',\n",
       " '(\"淘宝职位\")',\n",
       " '(\"物业管理\")',\n",
       " '(\"物流-仓储\")',\n",
       " '(\"环保-能源\")',\n",
       " '(\"生产管理-研发\")',\n",
       " '(\"电子-电气\")',\n",
       " '(\"短期零工\")',\n",
       " '(\"编辑-出版-印刷\")',\n",
       " '(\"美容-美发\")',\n",
       " '(\"美术-设计-创意\")',\n",
       " '(\"翻译\")',\n",
       " '(\"职业培训\")',\n",
       " '(\"计算机-互联网-通信\")',\n",
       " '(\"财务-审计-统计\")',\n",
       " '(\"质控-安防\")',\n",
       " '(\"贸易-采购\")',\n",
       " '(\"超市-百货-零售\")',\n",
       " '(\"运动健身\")',\n",
       " '(\"酒店\")',\n",
       " '(\"金融-银行-证券-投资\")',\n",
       " '(\"销售11\")',\n",
       " '(\"餐饮\")',\n",
       " '(\"餐饮零工\")',\n",
       " '(\"高级管理\")']"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(1054, \"Unknown column 'content' in 'field list'\")",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-42-b263c82db5cf>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 执行sql语句\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[0mcs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mcs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mcs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    161\u001b[0m         \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    162\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 163\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    164\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    165\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36m_query\u001b[1;34m(self, q)\u001b[0m\n\u001b[0;32m    319\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mq\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    320\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_clear_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 321\u001b[1;33m         \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mq\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    322\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    323\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mquery\u001b[1;34m(self, sql, unbuffered)\u001b[0m\n\u001b[0;32m    503\u001b[0m                 \u001b[0msql\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'surrogateescape'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    504\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 505\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    506\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    507\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[1;34m(self, unbuffered)\u001b[0m\n\u001b[0;32m    722\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    723\u001b[0m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 724\u001b[1;33m             \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    725\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    726\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mserver_status\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mread\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1067\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1068\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1069\u001b[1;33m             \u001b[0mfirst_packet\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1070\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1071\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mfirst_packet\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_ok_packet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[1;34m(self, packet_type)\u001b[0m\n\u001b[0;32m    674\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    675\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 676\u001b[1;33m             \u001b[0mpacket\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_for_error\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    677\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    678\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\protocol.py\u001b[0m in \u001b[0;36mraise_for_error\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    221\u001b[0m         \u001b[0merrno\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_uint16\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    222\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"errno =\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 223\u001b[1;33m         \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    224\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    225\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdump\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m    105\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    106\u001b[0m         \u001b[0merrorclass\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mInternalError\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[1;33m<\u001b[0m \u001b[1;36m1000\u001b[0m \u001b[1;32melse\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 107\u001b[1;33m     \u001b[1;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m: (1054, \"Unknown column 'content' in 'field list'\")"
     ]
    }
   ],
   "source": [
    "# 执行sql语句\n",
    "cs = conn.cursor()\n",
    "cs.execute(sql)\n",
    "conn.commit()\n",
    "cs.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>手机号</th>\n",
       "      <th>种类</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>94a20c908ea398cefba903e2d6598ea2</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5abd29bae58e80cc2513b9aa0f3dd598</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90875eb5b8fc1c84cf9a395f642ac092</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05f8673bd351bdb9bf3f503013784da8</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f4e507f349ac8455d8426db77c377636</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                手机号        种类\n",
       "0  94a20c908ea398cefba903e2d6598ea2  人事-行政-后勤\n",
       "1  5abd29bae58e80cc2513b9aa0f3dd598  人事-行政-后勤\n",
       "2  90875eb5b8fc1c84cf9a395f642ac092  人事-行政-后勤\n",
       "3  05f8673bd351bdb9bf3f503013784da8  人事-行政-后勤\n",
       "4  f4e507f349ac8455d8426db77c377636  人事-行政-后勤"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取顾客信息中的全部数据，格式：{'sheet_name':dataframe}\n",
    "customer_pd_dict = pd.read_excel('F:\\软件\\pycharm\\资料\\第三周课程课件\\第三周第十节项目资料\\数据\\顾客信息.xlsx',sheet_name=None)\n",
    "# 每个dataframe添加种类别\n",
    "for key in customer_pd_dict.keys():\n",
    "    customer_pd_dict[key]['种类'] = key\n",
    "# 合并多个dataframe\n",
    "df_customer = pd.concat([customer_pd_dict[key] for key in customer_pd_dict.keys()],axis = 0)\n",
    "df_customer.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<bound method NDFrame.head of                                   tel  category\n",
       "0    94a20c908ea398cefba903e2d6598ea2  人事-行政-后勤\n",
       "1    5abd29bae58e80cc2513b9aa0f3dd598  人事-行政-后勤\n",
       "2    90875eb5b8fc1c84cf9a395f642ac092  人事-行政-后勤\n",
       "3    05f8673bd351bdb9bf3f503013784da8  人事-行政-后勤\n",
       "4    f4e507f349ac8455d8426db77c377636  人事-行政-后勤\n",
       "..                                ...       ...\n",
       "388  a65aefdaeefec36a1050388ca7898d71      高级管理\n",
       "389  a431c672c62d3e3abbf0d438a1854129      高级管理\n",
       "390  d7d2f00bd2102105085a36dd3383e7e0      高级管理\n",
       "391  3847ac14c994d8f3d3c00d7ef22bcc62      高级管理\n",
       "392  b6323123ae73a9ada0fd62f7d72dba01      高级管理\n",
       "\n",
       "[30000 rows x 2 columns]>"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 更改列名\n",
    "df_customer.columns = ['tel','category']\n",
    "df_customer.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'select id,content from category;': (1054, \"Unknown column 'content' in 'field list'\")",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1680\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1681\u001b[1;33m             \u001b[0mcur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1682\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    162\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 163\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    164\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36m_query\u001b[1;34m(self, q)\u001b[0m\n\u001b[0;32m    320\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_clear_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 321\u001b[1;33m         \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mq\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    322\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mquery\u001b[1;34m(self, sql, unbuffered)\u001b[0m\n\u001b[0;32m    504\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 505\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    506\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[1;34m(self, unbuffered)\u001b[0m\n\u001b[0;32m    723\u001b[0m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 724\u001b[1;33m             \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    725\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36mread\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1068\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1069\u001b[1;33m             \u001b[0mfirst_packet\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1070\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[1;34m(self, packet_type)\u001b[0m\n\u001b[0;32m    675\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 676\u001b[1;33m             \u001b[0mpacket\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_for_error\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    677\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\protocol.py\u001b[0m in \u001b[0;36mraise_for_error\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    222\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"errno =\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 223\u001b[1;33m         \u001b[0merr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    224\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m    106\u001b[0m         \u001b[0merrorclass\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mInternalError\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[1;33m<\u001b[0m \u001b[1;36m1000\u001b[0m \u001b[1;32melse\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 107\u001b[1;33m     \u001b[1;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m: (1054, \"Unknown column 'content' in 'field list'\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-46-7fd5f12412c1>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 建立category到category_id的映射\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;31m# 读取category1\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mdf_category\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'select id,content from category;'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m \u001b[0mdf_category\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mread_sql\u001b[1;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[0;32m    487\u001b[0m             \u001b[0mcoerce_float\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcoerce_float\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    488\u001b[0m             \u001b[0mparse_dates\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mparse_dates\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 489\u001b[1;33m             \u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    490\u001b[0m         )\n\u001b[0;32m    491\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mread_query\u001b[1;34m(self, sql, index_col, coerce_float, params, parse_dates, chunksize)\u001b[0m\n\u001b[0;32m   1725\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1726\u001b[0m         \u001b[0margs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1727\u001b[1;33m         \u001b[0mcursor\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1728\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mcol_desc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mcol_desc\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdescription\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1729\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1691\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1692\u001b[0m             \u001b[0mex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mDatabaseError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"Execution failed on sql '{args[0]}': {exc}\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1693\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mex\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1694\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1695\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mDatabaseError\u001b[0m: Execution failed on sql 'select id,content from category;': (1054, \"Unknown column 'content' in 'field list'\")"
     ]
    }
   ],
   "source": [
    "# 建立category到category_id的映射\n",
    "# 读取category1\n",
    "df_category = pd.read_sql('select id,content from category;',conn)\n",
    "df_category.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_category' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-47-1371d8b535fb>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf_category\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mindex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_category\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'content'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdf_category\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df_category' is not defined"
     ]
    }
   ],
   "source": [
    "df_category.index = df_category['content'].values\n",
    "df_category.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_category' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-48-ae9134d608e6>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mcategory_content_to_id_dict\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_category\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_dict\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'id'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mcategory_content_to_id_dict\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df_category' is not defined"
     ]
    }
   ],
   "source": [
    "category_content_to_id_dict = df_category.to_dict()['id']\n",
    "category_content_to_id_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'category_content_to_id_dict' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-49-0ad51d5b92ba>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 修正df_customer中的category为category_id\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf_customer\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'category'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_customer\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'category'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mcategory\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mcategory_content_to_id_dict\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcategory\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mdf_customer\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, func, convert_dtype, args, **kwds)\u001b[0m\n\u001b[0;32m   4198\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4199\u001b[0m                 \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobject\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4200\u001b[1;33m                 \u001b[0mmapped\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlib\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmap_infer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconvert\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mconvert_dtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4201\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4202\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmapped\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mSeries\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\lib.pyx\u001b[0m in \u001b[0;36mpandas._libs.lib.map_infer\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32m<ipython-input-49-0ad51d5b92ba>\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(category)\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 修正df_customer中的category为category_id\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf_customer\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'category'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_customer\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'category'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mcategory\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mcategory_content_to_id_dict\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcategory\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mdf_customer\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'category_content_to_id_dict' is not defined"
     ]
    }
   ],
   "source": [
    "# 修正df_customer中的category为category_id\n",
    "df_customer['category'] = df_customer['category'].apply(lambda category: category_content_to_id_dict[category])\n",
    "df_customer.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tel</th>\n",
       "      <th>category_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>94a20c908ea398cefba903e2d6598ea2</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5abd29bae58e80cc2513b9aa0f3dd598</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>90875eb5b8fc1c84cf9a395f642ac092</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05f8673bd351bdb9bf3f503013784da8</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>f4e507f349ac8455d8426db77c377636</td>\n",
       "      <td>人事-行政-后勤</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                tel category_id\n",
       "0  94a20c908ea398cefba903e2d6598ea2    人事-行政-后勤\n",
       "1  5abd29bae58e80cc2513b9aa0f3dd598    人事-行政-后勤\n",
       "2  90875eb5b8fc1c84cf9a395f642ac092    人事-行政-后勤\n",
       "3  05f8673bd351bdb9bf3f503013784da8    人事-行政-后勤\n",
       "4  f4e507f349ac8455d8426db77c377636    人事-行政-后勤"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 修正列名字\n",
    "df_customer.columns = ['tel','category_id']\n",
    "df_customer.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1680\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1681\u001b[1;33m             \u001b[0mcur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1682\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    160\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 161\u001b[1;33m         \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0margs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    162\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pymysql\\cursors.py\u001b[0m in \u001b[0;36mmogrify\u001b[1;34m(self, query, args)\u001b[0m\n\u001b[0;32m    139\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0margs\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 140\u001b[1;33m             \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mquery\u001b[0m \u001b[1;33m%\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_escape_args\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    141\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: not all arguments converted during string formatting",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-51-5428ed142980>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 6\u001b[1;33m if_exists='append',)\n\u001b[0m",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m   2610\u001b[0m             \u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2611\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2612\u001b[1;33m             \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2613\u001b[0m         )\n\u001b[0;32m   2614\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m    596\u001b[0m         \u001b[0mchunksize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    597\u001b[0m         \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 598\u001b[1;33m         \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    599\u001b[0m     )\n\u001b[0;32m    600\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[1;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m   1825\u001b[0m             \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1826\u001b[0m         )\n\u001b[1;32m-> 1827\u001b[1;33m         \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1828\u001b[0m         \u001b[0mtable\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1829\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mcreate\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    719\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    720\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 721\u001b[1;33m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    722\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"fail\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    723\u001b[0m                 \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"Table '{self.name}' already exists.\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexists\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    706\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    707\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mexists\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 708\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpd_sql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhas_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mschema\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    709\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    710\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0msql_schema\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mhas_table\u001b[1;34m(self, name, schema)\u001b[0m\n\u001b[0;32m   1836\u001b[0m         \u001b[0mquery\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34mf\"SELECT name FROM sqlite_master WHERE type='table' AND name={wld};\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1837\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1838\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m>\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1839\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1840\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mget_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtable_name\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\sql.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1691\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1692\u001b[0m             \u001b[0mex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mDatabaseError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"Execution failed on sql '{args[0]}': {exc}\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1693\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mex\u001b[0m \u001b[1;32mfrom\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1694\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1695\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting"
     ]
    }
   ],
   "source": [
    "# 写入数据库\n",
    "df_customer.to_sql(\n",
    "'customer',\n",
    "conn,\n",
    "index=False,\n",
    "if_exists='append',)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: 'F:\\\\软件\\\\pycharm\\\\资料\\\\第三周课程课件\\\\第三周第十节项目资料\\\\数据\\x820-06月销售任务清单.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-53-58baec9e4b9d>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 沟通结果：读取任务表\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mmission_df_dict\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'F:\\软件\\pycharm\\资料\\第三周课程课件\\第三周第十节项目资料\\数据\\2020-06月销售任务清单.xlsx'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0msheet_name\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mmission_df_dict\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\util\\_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    294\u001b[0m                 )\n\u001b[0;32m    295\u001b[0m                 \u001b[0mwarnings\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwarn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mFutureWarning\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstacklevel\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 296\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    297\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    298\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols)\u001b[0m\n\u001b[0;32m    302\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    303\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 304\u001b[1;33m         \u001b[0mio\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mExcelFile\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mio\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    305\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m!=\u001b[0m \u001b[0mio\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    306\u001b[0m         raise ValueError(\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, path_or_buffer, engine)\u001b[0m\n\u001b[0;32m    865\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_io\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstringify_path\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    866\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 867\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engines\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_io\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    868\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    869\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__fspath__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\excel\\_xlrd.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, filepath_or_buffer)\u001b[0m\n\u001b[0;32m     20\u001b[0m         \u001b[0merr_msg\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"Install xlrd >= 1.0.0 for Excel support\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     21\u001b[0m         \u001b[0mimport_optional_dependency\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"xlrd\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mextra\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merr_msg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 22\u001b[1;33m         \u001b[0msuper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__init__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     23\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     24\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\excel\\_base.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, filepath_or_buffer)\u001b[0m\n\u001b[0;32m    351\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbook\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    352\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 353\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbook\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    354\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mbytes\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    355\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbook\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mload_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mBytesIO\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\pandas\\io\\excel\\_xlrd.py\u001b[0m in \u001b[0;36mload_workbook\u001b[1;34m(self, filepath_or_buffer)\u001b[0m\n\u001b[0;32m     35\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mopen_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfile_contents\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     36\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 37\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mopen_workbook\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     38\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     39\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\program files (x86)\\python\\lib\\site-packages\\xlrd\\__init__.py\u001b[0m in \u001b[0;36mopen_workbook\u001b[1;34m(filename, logfile, verbosity, use_mmap, file_contents, encoding_override, formatting_info, on_demand, ragged_rows)\u001b[0m\n\u001b[0;32m    109\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    110\u001b[0m         \u001b[0mfilename\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexpanduser\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilename\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 111\u001b[1;33m         \u001b[1;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilename\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"rb\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    112\u001b[0m             \u001b[0mpeek\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpeeksz\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    113\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mpeek\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34mb\"PK\\x03\\x04\"\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;31m# a ZIP file\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: 'F:\\\\软件\\\\pycharm\\\\资料\\\\第三周课程课件\\\\第三周第十节项目资料\\\\数据\\x820-06月销售任务清单.xlsx'"
     ]
    }
   ],
   "source": [
    "# 沟通结果：读取任务表\n",
    "mission_df_dict = pd.read_excel('F:\\软件\\pycharm\\资料\\第三周课程课件\\第三周第十节项目资料\\数据\\2020-06月销售任务清单.xlsx',sheet_name = None)\n",
    "mission_df_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'mission_df_dict' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-55-ff07ff254ff0>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 添加日期列\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[1;32mfor\u001b[0m \u001b[0mcreateDate\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mmission_df_dict\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m     \u001b[0mmission_df_dict\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcreateDate\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'createDate'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcreateDate\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'mission_df_dict' is not defined"
     ]
    }
   ],
   "source": [
    "# 添加日期列\n",
    "for createDate in mission_df_dict.keys():\n",
    "    mission_df_dict[createDate]['createDate'] = createDate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_mission_pd' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-56-233f8a5bd8f6>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 合并任务表\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf_mission_pd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mmission_df_dict\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcreateDate\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mcreateDate\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mmission_df_dicr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df_mission_pd' is not defined"
     ]
    }
   ],
   "source": [
    "# 合并任务表\n",
    "df_mission_pd.concat([mission_df_dict[createDate] for createDate in mission_df_dicr.keys()],axis=0)\n",
    "df_mission.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_mission' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-57-ca2889d60af2>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 修正列名\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mresultt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'result'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtolist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mresultt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df_mission' is not defined"
     ]
    }
   ],
   "source": [
    "# 修正列名\n",
    "resultt = df_mission['result'].unique().tolist()\n",
    "resultt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'resultt' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-58-219aefa6df03>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 创造result的dataframe,用来写入MySQL\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m df_result = pd.DataFrame(resultt,\n\u001b[0m\u001b[0;32m      3\u001b[0m                         colums = ['content'],)\n\u001b[0;32m      4\u001b[0m \u001b[0mdf_status\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'resultt' is not defined"
     ]
    }
   ],
   "source": [
    "# 创造result的dataframe,用来写入MySQL\n",
    "df_result = pd.DataFrame(resultt,\n",
    "                        colums = ['content'],)\n",
    "df_status.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_status' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-59-8c768c0da8f7>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 写入数据库\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m df_status.to_sql('result',\n\u001b[0m\u001b[0;32m      3\u001b[0m                 \u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m                 \u001b[0mindex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m                 if_exists='append')\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df_status' is not defined"
     ]
    }
   ],
   "source": [
    "# 写入数据库\n",
    "df_status.to_sql('result',\n",
    "                conn,\n",
    "                index = False,\n",
    "                if_exists='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_mission' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-60-cb0632affd39>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 沟通任务\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf_mission\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'df_mission' is not defined"
     ]
    }
   ],
   "source": [
    "# 沟通任务\n",
    "df_mission.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id, tel]\n",
       "Index: []"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tel--> customer.id\n",
    "# 查询customer\n",
    "df_customer = pd.read_sql('select id,tel from customer;',\n",
    "                         conn)\n",
    "df_customer.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{}"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customer_tel_to_id_dict = df_customer.to_dict()['id']\n",
    "customer_tel_to_id_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{}"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# salesstaff name-->salesstaff.id\n",
    "# 查询salesstaff\n",
    "df_salesstaff = pd.read_sql('select id,name from salesstaff;',\n",
    "                           conn)\n",
    "df_salesstaff.index = df_salesstaff['name'].values\n",
    "salesstaff_name_to_id_dict = df_salesstaff.to_dict()['id']\n",
    "salesstaff_name_to_id_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-64-c6c02b819da9>, line 4)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-64-c6c02b819da9>\"\u001b[1;36m, line \u001b[1;32m4\u001b[0m\n\u001b[1;33m    conn)\u001b[0m\n\u001b[1;37m       ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# result-->result.id\n",
    "# 查询salesstaff\n",
    "df_result = pd.read_sql('select id,content from result;'\n",
    "                       conn)\n",
    "df_status.index = df_status['cuntent'].values\n",
    "status_content_to_id_dict = df_status.to_dict()['id']\n",
    "status_content_to_id_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_mission' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-65-2e7759a5e1bb>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 变更外键\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf_mission\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'tel'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'tel'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mtel\u001b[0m\u001b[1;33m:\u001b[0m\u001b[0mcustomer_tel_to_id_dict\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mtel\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'salesstaffJ_name'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'salesstaff_name'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m:\u001b[0m\u001b[0msalesstaff_name_to_id_dict\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'result'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'result'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mlambda\u001b[0m \u001b[0mstatus\u001b[0m\u001b[1;33m:\u001b[0m\u001b[0mstatus_content_to_id_dict\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mresult\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df_mission' is not defined"
     ]
    }
   ],
   "source": [
    "# 变更外键\n",
    "df_mission['tel'] = df_mission['tel'].apply(lambda tel:customer_tel_to_id_dict[tel])\n",
    "df_mission['salesstaffJ_name'] = df_mission['salesstaff_name'].apply(lambda name:salesstaff_name_to_id_dict[name])\n",
    "df_mission['result'] = df_mission['result'].apply(lambda status:status_content_to_id_dict[result])\n",
    "df_mission.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_mission' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-66-bc2e0cd39838>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 修改列名\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf_mission\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;34m'customer_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'salesstaff_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'result_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'createDate'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mdf_mission\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df_mission' is not defined"
     ]
    }
   ],
   "source": [
    "# 修改列名\n",
    "df_mission.columns = ['customer_id','salesstaff_id','result_id','createDate']\n",
    "df_mission.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_mission' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-67-dfc88e6452f9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf_mission\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'mission'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mif_exists\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'append'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'df_mission' is not defined"
     ]
    }
   ],
   "source": [
    "df_mission.to_sql('mission',conn,index=False,if_exists='append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
